﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;
using System.Windows.Forms;
using DatabaseCore;
using Eaktest;
using SystemCore;
using Excel = Microsoft.Office.Interop.Excel;

namespace EakTest
{
    public partial class UploadSaleOrder : Form
    {
        private const string C_SHEET_NAME = "Check";
        private const string C_KEY_ORDER = "Order";

        IExcelDao excelDao = new ExcelDao();
        IGenerateNoDao generateNoDao = new GenerateNoDao();
        IOrderDao orderDao = new OrderDao();
        IOrderDetailDao orderDetailDao = new OrderDetailDao();
        ISapDao sapDao = new SapDao();

        public UploadSaleOrder()
        {
            InitializeComponent();
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Filter = @"Excel Files|*.xls;*.xlsx;*.xlsm;*.csv";

            DialogResult result = dialog.ShowDialog();
            if (result == DialogResult.OK)
            {
                txtBrowse.Text = dialog.FileName;
            }
        }

        private void btnUpload_Click(object sender, EventArgs e)
        {
            //string fileName = @"C:\Users\Risako\Desktop\NWCTEST.xlsx";
            string fileName = txtBrowse.Text;
            if (string.IsNullOrEmpty(fileName))
            {
                MessageDialog.Information("Please browse file for upload.");
                return;
            }

            try
            {
                using (var ts = new TransactionScope())
                {
                    UploadExcel(fileName);
                    ts.Complete();
                }
            }
            catch (Exception ex)
            {
                MessageDialog.Error(ex.Message);
            }
        }

        #region Private Method

        #region Excel
        private void UploadExcel(string fileName)
        {
            Excel.Application xlApp = null;
            Excel.Workbook xlWorkBook = null;
            Excel.Worksheet xlWorkSheet = null;
            Excel.Range xlRange = null;

            try
            {
                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Open(fileName);
                xlWorkSheet = xlWorkBook.Sheets[C_SHEET_NAME];
                xlRange = xlWorkSheet.UsedRange;

                int rowCount = xlRange.Rows.Count;
                int colCount = xlRange.Columns.Count;

                //if(!isTemplate)
                //    MessageDialog.Information("Invalid file, template does not match.");

                //if(isError)
                //    MessageDialog.Information("Cannot upload file.");

                tbl_Order order = new tbl_Order();
                MappingOrderData(xlRange, order);
                List<tbl_OrderDetail> orderDetail = new List<tbl_OrderDetail>(); ;
                MappingOrderDetailData(xlRange, order, orderDetail);

                orderDao.Insert(order);
                orderDetailDao.Insert(orderDetail);

                tbl_ExcelFile excel = null;
                MappingExcelData(fileName, out excel);
                excelDao.Insert(excel);

                MessageDialog.Information("Upload successful.");
            }
            catch (Exception ex)
            {
                MessageDialog.Error(ex.Message);
            }
            finally
            {
                xlWorkBook.Close(false, null, null);
                xlApp.Quit();

                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
            }
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageDialog.Error("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

        #endregion

        #region Mapping Data
        private void MappingExcelData(string fileName, out tbl_ExcelFile excel)
        {
            excel = new tbl_ExcelFile();
            excel.PathName = fileName;

            excel.CreateBy = UserContext.FullName;
            excel.CreateDate = DateTime.Now;
        }

        private void MappingOrderData(Excel.Range xlRange, tbl_Order order)
        {
            //order = new tbl_Order();

            order.OrderNo = GenerateOrderNo();
            order.RefOrderNo = xlRange.Cells[5, "C"].Value2.ToString().Trim();
            order.OrderDate = DateTime.FromOADate(xlRange.Cells[5, "E"].Value2);
            order.InvoiceNo = xlRange.Cells[5, "J"].Value2.ToString().Trim();
            order.PortOfShipment = xlRange.Cells[6, "C"].Value2.ToString().Trim();
            order.PortOfDestination = xlRange.Cells[6, "H"].Value2.ToString().Trim();
            //order.BuyerCode = xlRange.Cells["", ""].Value2.ToString().Trim();
            //order.BuyerName = xlRange.Cells["", ""].Value2.ToString().Trim();
            //order.ShipmentNote = xlRange.Cells["", ""].Value2.ToString().Trim();
            //order.ConsigneeName = xlRange.Cells["", ""].Value2.ToString().Trim();
            order.Payment = xlRange.Cells[11, "J"].Value2.ToString().Trim();
            //order.PONo = xlRange.Cells["", ""].Value2.ToString().Trim();
            //order.Location = xlRange.Cells["", ""].Value2.ToString().Trim();

            //อ่านผลรวมที่ detail
            //order.TotalQty = Convert.ToInt32(xlRange.Cells[24, "K"].Value2);
            //order.TotalPackage = Convert.ToInt32(xlRange.Cells[25, "K"].Value2);
            //order.TotalAmount = Convert.ToDecimal(xlRange.Cells[26, "K"].Value2);
            //order.TotalWeight = Convert.ToDecimal(xlRange.Cells[27, "K"].Value2);

            order.Status = (int)EOrderStatus.Order;

            order.CreateBy = UserContext.FullName;
            order.CreateDate = DateTime.Now;
            order.UpdateBy = UserContext.FullName;
            order.UpdateDate = DateTime.Now;
        }

        private void MappingOrderDetailData(Excel.Range xlRange, tbl_Order order, List<tbl_OrderDetail> orderDetailList)
        {
            //order = null;
            //orderDetailList = new List<tbl_OrderDetail>();

            string orderNo = order.OrderNo;
            int record = 14;
            while (xlRange.Cells[record, "A"].Value2 != null)
            {
                string productCode = xlRange.Cells[record, "D"].Value2.ToString().Trim();

                tbl_OrderDetail orderDetail = new tbl_OrderDetail();

                orderDetail.OrderNo = orderNo;
                orderDetail.ItemNo = Convert.ToInt32(xlRange.Cells[record, "A"].Value2);
                orderDetail.ItemId = xlRange.Cells[record, "B"].Value2.ToString().Trim();
                //orderDetail.ItemName;
                orderDetail.ProductId = productCode;
                //orderDetail.ProductName;
                orderDetail.Qty = Convert.ToInt32(xlRange.Cells[record, "E"].Value2.ToString());
                orderDetail.ReceivedQty = 0;
                orderDetail.RemainingQty = Convert.ToInt32(xlRange.Cells[record, "E"].Value2.ToString());
                orderDetail.UnitPrice = Convert.ToDecimal(xlRange.Cells[record, "F"].Value2.ToString());
                orderDetail.Amount = Convert.ToDecimal(xlRange.Cells[record, "G"].Value2.ToString());
                orderDetail.PcsPerBox = Convert.ToInt32(xlRange.Cells[record, "H"].Value2.ToString());
                orderDetail.Package = Convert.ToInt32(xlRange.Cells[record, "I"].Value2.ToString());
                orderDetail.WeightPerUnit = Convert.ToDecimal(xlRange.Cells[record, "J"].Value2.ToString());
                orderDetail.Weight = Convert.ToDecimal(xlRange.Cells[record, "K"].Value2.ToString());
                orderDetail.MaterialNo = xlRange.Cells[record, "L"].Value2.ToString().Trim(); //sapDao.GetMaterialNo(productCode);
                orderDetail.Plant = xlRange.Cells[record, "N"].Value2.ToString().Trim(); //GetFromSAP();

                orderDetail.CreateBy = UserContext.FullName;
                orderDetail.CreateDate = DateTime.Now;
                orderDetail.UpdateBy = UserContext.FullName;
                orderDetail.UpdateDate = DateTime.Now;

                orderDetailList.Add(orderDetail);
                record++;
            }

            //Get header total
            order.TotalQty = Convert.ToInt32(xlRange.Cells[record, "K"].Value2);
            order.TotalPackage = Convert.ToInt32(xlRange.Cells[record + 1, "K"].Value2);
            order.TotalAmount = Convert.ToDecimal(xlRange.Cells[record + 2, "K"].Value2);
            order.TotalWeight = Convert.ToDecimal(xlRange.Cells[record + 3, "K"].Value2);
        }
        #endregion

        private string GenerateOrderNo()
        {
            int runningNo = generateNoDao.GenerateNo(C_KEY_ORDER);
            string orderNo = string.Format("{0}{1}", DateTime.Now.ToString("ddMMyy", new CultureInfo("en-US")), runningNo.ToString("000000"));
            return orderNo;
        }

        #endregion
    }
}
